﻿//功能描述：领导驾驶舱数据访问层
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Dapper;

using Hong.Common.Encryption;
using Hong.IRepository.IRepository_LeadingCockpit;
using Hong.Model.SysDomain.Model_ContractManagement;
using Hong.Model.SysDomain.Model_CustomerManagement;
using Hong.Model.SysDomain.Model_LeadingCockpitManagement;
using Hong.Model.SysDomain.Model_OperationManagement;
using Hong.Model.SysDomain.Model_SystemSettings;
using Hong.Repository.Data;

namespace Hong.Repository.Repository.Repository_LeadingCockpit
{
    public class LeadingCockpitRepository: ILeadingCockpitRepository
    {
        #region 构造器注入
        private readonly T_HongShi_3Context db;
        private readonly SecurityStatic _securityStatic;
        public LeadingCockpitRepository(T_HongShi_3Context _db, SecurityStatic securityStatic)
        {
            db = _db;
            _securityStatic = securityStatic;
        }
        #endregion

        #region 领导驾驶舱

        #region 合同与项目综合统计（显示、查询）
        public List<dynamic> GetStatistics(string DName = "")
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var sql = "select a.DepName DName,count(b.CustId) clerkid,count(c.contractId) proid,count(d.projectId) conid from Department a left join Customer b on a.DepId = b.DepartmentId left join ContractInfo c on b.CustId = c.ClientId left join Project d on c.ContractId = d.ProjectConId group by a.DepName having 1 = 1";
                var list = conn.Query<dynamic>(sql).ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 合同与项目综合统计导出
        public List<dynamic> GetExecel()
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var sql = "select a.DepName,count(b.CustId) clerkid,count(c.contractId) proid,count(d.projectId) conid from Department a left join Customer b on a.DepId = b.DepartmentId left join ContractInfo c on b.CustId = c.ClientId left join Project d on c.contractId = d.projectConId group by a.DepName having 1 = 1";
                var list = conn.Query<dynamic>(sql).ToList();

                return list;
            }
        }
        #endregion

        #region 合同收款综合统计
        public Page GetContInfo(int pageindex,int pagesize,string ContractName="",string OriginalContractPrice="",string ContractNumber="", string RealyContractPrice="",string ClientId="")
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    Page page = new Page();
                    var list = conn.Query<ContractInfo>("select a.*,b.personNmae PName from ContractInfo a join Person b on a.clientId=b.personId").ToList();
                    if (!string.IsNullOrEmpty(ContractName))
                    {
                        list = list.Where(a => a.ContractName.Contains(ContractName)).ToList();
                    }
                    if (!string.IsNullOrEmpty(OriginalContractPrice))
                    {
                        list = list.Where(a => a.OriginalContractPrice.Contains(OriginalContractPrice)).ToList();
                    }
                    if (!string.IsNullOrEmpty(ContractNumber))
                    {
                        list = list.Where(a => a.ContractNumber.Contains(ContractNumber)).ToList();
                    }
                    if (!string.IsNullOrEmpty(RealyContractPrice))
                    {
                        list = list.Where(a => a.RealyContractPrice.Contains(RealyContractPrice)).ToList();
                    }
                    if (!string.IsNullOrEmpty(ClientId))
                    {
                        list = list.Where(a => a.ClientId.Contains(ClientId)).ToList();
                    }
                    page.totalCount = list.Count();
                    list = list.Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                    page.Data = list;
                    return page;
                }


            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        #region 合同收费综合统计导出
        public List<dynamic> GetExecel1()
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var sql = "select * from ContractInfo";
                var list = conn.Query<dynamic>(sql).ToList();

                return list;
            }
        }
        #endregion

        #region 合同收费反填
        public ContractInfo FindInfo(string ContractId)
        {
            try
            {
                var list = db.ContractInfos.Find(ContractId);
                return list;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目综合统计
        public List<Project> GetProject(string DName, string Conid, int?Scaleid, int?Lxid, int?Jxid, int?Ztid, int?Wcid)
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Project>("select a.DepName DName, count(b.projectId) conid,sum(b.projectScale) scaleid,sum(b.projectLx) lxid,sum(b.projectJx) jxid,sum(b.projectZt) ztid,sum(b.projectWc) wcid from Department a left join Project b on a.DepId = b.projectDeptID group by a.DepName having 1 = 1").ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                if (!string.IsNullOrEmpty(Conid))
                {
                    list = list.Where(a => a.Conid.Contains(Conid)).ToList();
                }
                if (Scaleid != null)
                {
                    list = list.Where(b => b.Scaleid == Scaleid).ToList();
                }
                if (Lxid != null)
                {
                    list = list.Where(b => b.Lxid == Lxid).ToList();
                }
                if (Jxid != null)
                {
                    list = list.Where(b => b.Jxid == Jxid).ToList();
                }
                if (Ztid != null)
                {
                    list = list.Where(b => b.Ztid == Ztid).ToList();
                }
                if (Wcid != null)
                {
                    list = list.Where(b => b.Wcid == Wcid).ToList();
                }



                return list;
            }
        }
        #endregion

        #region 获取部门信息
        public List<Department> GetDept()
        {
            using (IDbConnection conn=new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Department>("select * from Department").ToList();
                return list;
            }
        }
        #endregion

        #region 获取甲方联系人
        public List<Person> ShowReponsble()
        {
            using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Person>("select * from Person").ToList();
                return list;
            }
        }
        #endregion

        #region 部门综合统计
        public List<dynamic> GetContract(string D="",string Y="",string O="",string R="",string W="",string C="")
        {
            using(IDbConnection conn=new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<dynamic>("select a.depName as D,b.year as Y,c.originalContractPrice as O,c.realyContractPrice as R,b.wcbl as W,c.createTime as C from Department a join DeptContract b on a.depId = b.deptId join ContractInfo c on b.htId = c.contractId").ToList();
                if (!string.IsNullOrEmpty(D))
                {
                    list = list.Where(a => a.D.Contains(D)).ToList();
                }
                if (!string.IsNullOrEmpty(Y))
                {
                    list = list.Where(a => a.Y.Contains(Y)).ToList();
                }
                if (!string.IsNullOrEmpty(O))
                {
                    list = list.Where(a => a.O.Contains(O)).ToList();
                }
                if (!string.IsNullOrEmpty(R))
                {
                    list = list.Where(a => a.R.Contains(R)).ToList();
                }
                if (!string.IsNullOrEmpty(W))
                {
                    list = list.Where(a => a.W.Contains(W)).ToList();
                }
                if (!string.IsNullOrEmpty(C))
                {
                    list = list.Where(a => a.C.Contains(C)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 部门合同额同比(累计)
        public List<ContractDept> GetContract2s(string DName="")
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                conn.Open();
                var list = conn.Query<ContractDept>("select a.*,b.depName as DName from ContractDept a join Department b on a.DeptId=b.depId").ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 合同额对比统计(月)
        public List<ContractDept> GetContract3s(string DName = "")
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                conn.Open();
                var list = conn.Query<ContractDept>("select a.*,b.depName as DName from ContractDept a join Department b on a.DeptId=b.depId").ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 合同收费详情
        public List<ContractInfo> GetContract4s(string DName = "")
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                conn.Open();
                var list = conn.Query<ContractInfo>("select b.*,a.projectName as DName,a.projectScale as CName from ContractInfo b join Project a on b.contractId=a.projectConId").ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 获取项目信息
        public List<Project> GetProject2()
        {
            using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Project>("select * from Project").ToList();
                return list;
            }
        }
        #endregion

        public List<ProjectDuibi> GetProject4(string DName = "")
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                conn.Open();
                var list = conn.Query<ProjectDuibi>("select a.*,b.depName as DName from ProjectDuibi a join Department b on a.DepeId=b.DepId").ToList();
                if (!string.IsNullOrEmpty(DName))
                {
                    list = list.Where(a => a.DName.Contains(DName)).ToList();
                }
                return list;
            }
        }

        #endregion
    }
}
